import pandas as pd
from openai import OpenAI
from sklearn.metrics import accuracy_score
import ollama
from pandasai.llm.local_llm import LocalLLM
from pandasai import SmartDataframe
import numpy as np
import pandas as pd
from warnings import simplefilter
simplefilter(action="ignore", category=pd.errors.PerformanceWarning)



def query_to_llm(filename,model,query):
    df = SmartDataframe(filename, config={"llm": model})
    result = df.chat(query)
    return result

def truth_value(df,query):
    result = eval(query)
    return result


if __name__ == '__main__':
    filename = 'data/export_179004_0.csv'
    # Read the CSV files
    df1 = pd.read_csv('data/export_179004_0.csv')
    df2 = pd.read_csv('data/export_179004_1.csv')
    df3 = pd.read_csv('data/export_179004_2.csv', encoding='ISO-8859-1').astype(str).apply(lambda x: x.str.encode('utf-8').str.decode('utf-8'))

    # Ensure 'CrashId' column is of the same type in all dataframes
    df1['CrashId'] = df1['CrashId'].astype(str)
    df2['CrashId'] = df2['CrashId'].astype(str)
    df3['CrashId'] = df3['CrashId'].astype(str)

    # Merge the dataframes on the 'CrashId' column
    df_merged = df1.merge(df2, on='CrashId').merge(df3, on='CrashId')

    # Save the merged dataframe to a new CSV file
    df_merged.to_csv('data/merged_data.csv', index=False)

    # Update the filename to the merged data
    filename = 'data/merged_data.csv'

    df=pd.read_csv(filename,low_memory=False)

    column_ids = [84, 101, 114, 166, 183, 187, 188, 190, 194, 199, 204, 206, 276]
    column_names = df.columns[column_ids]

    for col_id in column_names:
        if col_id in df.columns:
            unique_values = df[col_id].unique()
            value_types = [type(value) for value in unique_values]
            print(f"Types for column {col_id}: {value_types}")
            print(f"Unique values for column {col_id}: {unique_values}")
        else:
            print(f"Column {col_id} not found in the dataframe.")

    for col_name in column_names:
        if col_name in df.columns:
            df[col_name] = df[col_name].astype(str).replace('nan', 'NULL')
    
    for col_id in column_names:
        if col_id in df.columns:
            unique_values = df[col_id].unique()
            if len(set(type(value) for value in unique_values)) == 1:
                print(f"All values in column {col_id} are of the same type.")
            else:
                print(f"Values in column {col_id} are of different types.")
        else:
            print(f"Column {col_id} not found in the dataframe.")

    df.to_csv('data/cleaned_data.csv', index=False)
    # model = LocalLLM(api_base="http://localhost:11434/v1", model="gemma2:27b")
    # result_llm = query_to_llm(filename,model,'which Town Name has the highest number of accidents?')

    
    